﻿' These methods were used during program development to dump certain database
' tables to text files. These files were then modified in emacs for loading
' into the database by the database tools.
'
' Note that this source file is excluded from the build. Review the methods
' carefully before turning these back on and using them.

Imports System.Data
Imports System.Data.SqlClient

Public Module DatabaseDump

    Public Sub DumpTables(ByVal connection As Connection)
        'DumpStateTable(connection)
        'DumpCityTable(connection)
        'DumpCountyTable(connection)
        DumpMetroTable(connection)
    End Sub

    Public Sub DumpMetroTable(ByVal connection As Connection)

        Trace.WriteLine("dumping Metro table")
        Trace.Flush()
        Dim cmd As SqlCommand = connection.CreateCommand
        cmd.CommandText = "SELECT ShapeID, ENTITYID, ENTITYNAME, STATE, Location.ToString() " & _
                          "FROM dbo.RAWMetroFromShape"

        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        If Not rdr.HasRows Then
            ReportError("No data in Metro table")
            rdr.Close()
            Return
        End If

        Dim ostr As New IO.StreamWriter("C:\metro2.txt", False)
        Dim count As Integer = 0
        While rdr.Read()
            count += 1
            ostr.WriteLine(String.Format("{0};{1};{2};{3};{4}", rdr.Item(0), rdr.Item(1), rdr.Item(2), rdr.Item(3), rdr.Item(4)))
            'ostr.WriteLine(String.Format("{0};{1};Metro{2};{3};{4}", rdr.Item(0), rdr.Item(0), rdr.Item(0), rdr.Item(3), rdr.Item(4)))
        End While
        Trace.WriteLine("dumped " & count & " records")
        ostr.Close()
        rdr.Close()
    End Sub

    Public Sub DumpStateTable(ByVal connection As Connection)

        Trace.WriteLine("dumping State table")
        Trace.Flush()
        Dim cmd As SqlCommand = connection.CreateCommand
        cmd.CommandText = "SELECT ShapeID, STATE_NAME, STATE_FIPS, SUB_REGION, STATE_ABBR, StatePolygon.ToString() " & _
                          "FROM dbo.States"

        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        If Not rdr.HasRows Then
            ReportError("No data in State table")
            rdr.Close()
            Return
        End If

        Dim ostr As New IO.StreamWriter("C:\states.txt", False)
        Dim count As Integer = 0
        While rdr.Read()
            count += 1
            ostr.WriteLine(String.Format("{0};{1};{2};{3};{4};{5}", rdr.Item(0), rdr.Item(1), rdr.Item(2), rdr.Item(3), rdr.Item(4), rdr.Item(5)))
        End While
        Trace.WriteLine("dumped " & count & " records")
        ostr.Close()
        rdr.Close()
    End Sub

    Public Sub DumpCityTable(ByVal connection As Connection)

        Trace.WriteLine("dumping Cities table")
        Trace.Flush()
        Dim cmd As SqlCommand = connection.CreateCommand
        cmd.CommandText = "SELECT ShapeID, ID, Name, ST, STATE, CityPoint.ToString() " & _
                          "FROM dbo.Cities"

        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        If Not rdr.HasRows Then
            ReportError("No data in Cities table")
            rdr.Close()
            Return
        End If

        Dim ostr As New IO.StreamWriter("C:\cities.txt", False)
        Dim ostr2 As New IO.StreamWriter("C:\citynames.txt", False)
        Dim count As Integer = 0
        While rdr.Read()
            count += 1
            Dim city As String = Capitalize(rdr.Item(2).ToString)
            Dim state As String = Capitalize(rdr.Item(4).ToString)
            ostr.WriteLine(String.Format("{0};{1};{2};{3};{4};{5}", rdr.Item(0), rdr.Item(1), city, rdr.Item(3), state, rdr.Item(5)))
            ostr2.WriteLine(city)
        End While
        Trace.WriteLine("dumped " & count & " records")
        ostr.Close()
        ostr2.Close()
        rdr.Close()
    End Sub

    Public Sub DumpCountyTable(ByVal connection As Connection)

        Trace.WriteLine("dumping Counties table")
        Trace.Flush()
        Dim cmd As SqlCommand = connection.CreateCommand
        cmd.CommandText = "SELECT ShapeID, NAME, STATE_NAME, STATE_FIPS, FIPS, CountyPolygon.ToString() " & _
                          "FROM dbo.Counties"

        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        If Not rdr.HasRows Then
            ReportError("No data in Counties table")
            rdr.Close()
            Return
        End If

        Dim ostr As New IO.StreamWriter("C:\counties.txt", False)
        Dim count As Integer = 0
        While rdr.Read()
            count += 1
            ostr.WriteLine(String.Format("{0};{1};{2};{3};{4};{5}", rdr.Item(0), rdr.Item(1), rdr.Item(2), rdr.Item(3), rdr.Item(4), rdr.Item(5)))
        End While
        Trace.WriteLine("dumped " & count & " records")
        ostr.Close()
        rdr.Close()
    End Sub

End Module
